﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using Models;
namespace DbControl
{
    public class DbHelper
    {
        public static string connectString ="";
        #region Method
        /// <summary>
        /// Insert
        /// </summary>
        public static int Insert(DeviceInfo data)
        {
            using (IDbConnection connection = new SQLiteConnection(connectString))
            {
                string sql = @"INSERT INTO DataInfos
           (DeviceId 
           ,DeviceName 
           ,DeviceMac 
           ,CreateTime 
           ,Fe1 
           ,Fe2 
           ,Fe3 
           ,Fe4 
           ,Fe5 
           ,Fe6 
           ,Fe7 
           ,Fe8 
           ,Fe9 
           ,Fe10 
           ,NFe1 
           ,NFe2 
           ,NFe3 
           ,NFe4 
           ,NFe5 
           ,MFe1 
           ,MFe2 
           ,MFe3 
           ,MFe4 
           ,MFe5 
           ,MFe6 
           ,MFe7 
           ,MFe8 
           ,MFe9 
           ,MFe10 
           ,Fe 
           ,NFe 
           ,MFe 
           ,Dens 
           ,Vis 
           ,Temp 
           ,Dc 
           ,Vol 
           ,Ppm 
           ,Aw 
           ,Ch4 
           ,Ch6 
           ,Ch14 
           ,Ch21 
           ,Isoch4 
           ,Isoch6 
           ,Isoch14 
           ,Isoch21 
           ,Nas)
     VALUES
           (@DeviceId  
           ,@DeviceName 
           ,@DeviceMac 
           ,@CreateTime 
           ,@Fe1  
           ,@Fe2  
           ,@Fe3  
           ,@Fe4  
           ,@Fe5  
           ,@Fe6  
           ,@Fe7  
           ,@Fe8  
           ,@Fe9  
           ,@Fe10  
           ,@NFe1  
           ,@NFe2  
           ,@NFe3  
           ,@NFe4  
           ,@NFe5  
           ,@MFe1  
           ,@MFe2  
           ,@MFe3  
           ,@MFe4  
           ,@MFe5  
           ,@MFe6  
           ,@MFe7  
           ,@MFe8  
           ,@MFe9  
           ,@MFe10  
           ,@Fe  
           ,@NFe  
           ,@MFe  
           ,@Dens
           ,@Vis  
           ,@Temp  
           ,@Dc
           ,@Vol
           ,@Ppm  
           ,@Aw 
           ,@Ch4  
           ,@Ch6  
           ,@Ch14  
           ,@Ch21  
           ,@Isoch4  
           ,@Isoch6  
           ,@Isoch14  
           ,@Isoch21  
           ,@Nas)";
                return connection.Execute(sql, data);
            }
        }
        /// <summary>
        /// Update
        /// </summary>
        public static int Update(DeviceInfo data)
        {
            using (IDbConnection connection = new SQLiteConnection(connectString))
            {
                string sql = @"UPDATE test SET      
                        way1 = 'way1',
                        way2 = 'way2',
                        way3 = 'way3',
                        way4 = 'way4',
                        way5 = 'way5',
                        way6 = 'way6',
                        way7 = 'way7',
                        way8 = 'way8',
                        adddate = 'adddate'
                        WHERE Id = @Id ";
                return connection.Execute(sql, data);
            }
        }
        /// <summary>
        /// Select
        /// </summary>
        public static List<DeviceInfo> Select()
        {
            using (IDbConnection connection = new SQLiteConnection(connectString))
            {
                string sql = @"SELECT Id
      ,DeviceId
      ,DeviceName
      ,DeviceMac
      ,CreateTime
      ,Fe1
      ,Fe2
      ,Fe3
      ,Fe4
      ,Fe5
      ,Fe6
      ,Fe7
      ,Fe8
      ,Fe9
      ,Fe10
      ,NFe1
      ,NFe2
      ,NFe3
      ,NFe4
      ,NFe5
      ,MFe1
      ,MFe2
      ,MFe3
      ,MFe4
      ,MFe5
      ,MFe6
      ,MFe7
      ,MFe8
      ,MFe9
      ,MFe10
      ,Fe
      ,NFe
      ,MFe
      ,Dens
      ,Vis
      ,Temp
      ,Dc
      ,Vol
      ,Ppm
      ,Aw
      ,Ch4
      ,Ch6
      ,Ch14
      ,Ch21
      ,Isoch4
      ,Isoch6
      ,Isoch14
      ,Isoch21
      ,Nas
  FROM DataInfos";
                return connection.Query<DeviceInfo>(sql).ToList();
            }
        }
        public static DataTable SelectForPrint(DateTime d1, DateTime d2)
        {
            using (IDbConnection connection = new SQLiteConnection(connectString))
            {
                string sql = @"SELECT Id
      ,DeviceId
      ,DeviceName
      ,DeviceMac
      ,CreateTime
      ,Fe1
      ,Fe2
      ,Fe3
      ,Fe4
      ,Fe5
      ,Fe6
      ,Fe7
      ,Fe8
      ,Fe9
      ,Fe10
      ,NFe1
      ,NFe2
      ,NFe3
      ,NFe4
      ,NFe5
      ,MFe1
      ,MFe2
      ,MFe3
      ,MFe4
      ,MFe5
      ,MFe6
      ,MFe7
      ,MFe8
      ,MFe9
      ,MFe10
      ,Fe
      ,NFe
      ,MFe
      ,Dens
      ,Vis
      ,Temp
      ,Dc
      ,Vol
      ,Ppm
      ,Aw
      ,Ch4
      ,Ch6
      ,Ch14
      ,Ch21
      ,Isoch4
      ,Isoch6
      ,Isoch14
      ,Isoch21
      ,Nas
  FROM DataInfos WHERE CreateTime between @t1 and @t2 order by Id asc";//,Id limit 0,1
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { t1 = d1, t2 = d2 }));
                return dt;
            }
        }
        public static DataTable SelectForPrintFD(DateTime d1, DateTime d2)
        {
            using (IDbConnection connection = new SQLiteConnection(connectString))
            {
                string sql = @"SELECT  Id,
                                 adddate,
                                 way1,
                                 way2,
                                 way3,
                                 way4,                                
                                 way5,                                
                                 way6,
                                 way7,
                                 way8,
                                 way9,
                                 way10,
                                 way11,
                                 way12,
                                 way13,
                                 way14,
                                 way15,
                                 way16,
                                 way17,
                                 way18,
                                 way19,
                                 way20,
                                 way21,
                                 way22,
                                 way23,
                                 way24,
                                 way25,
                                 way26,
                                 way27,
                                 way28,
                                 way29,
                                 way30,
                                 way31,
                                 way32,   
                                 way33,
                                 way34
  FROM test WHERE adddate between @t1 and @t2 order by Id asc";//,Id limit 0,1
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { t1 = d1, t2 = d2 }));
                return dt;
            }
        }
        public static DataTable SelectBySql(string sql)
        {
            using (IDbConnection connection = new SQLiteConnection(connectString))
            {
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }

        }
        #endregion
    }
}
